package com.eascs.admin.o2o.loan.dao;

import org.springframework.stereotype.Repository;
import com.eascs.admin.common.dao.BaseO2oDao;
import com.eascs.admin.o2o.internal.util.BaseUtils;
import com.eascs.admin.o2o.loan.form.LoanTimeSearchForm;
import com.eascs.app.dao.PageResult;
import com.eascs.app.dao.SqlPageQuery;
import com.eascs.common.util.StringUtil;

@Repository
public class LoanTimeDao extends BaseO2oDao{
	/**
	 * 贷款分页查询
	 * @param payMentSearchForm
	 * @return
	 */
	public PageResult<Object[]> findByPage(LoanTimeSearchForm form ,String type) {
		int pageindex = form.getPageIndex() == null ? BaseUtils.PAGE_INDEX : form.getPageIndex();
		SqlPageQuery<Object[]> pageQuery = new SqlPageQuery<Object[]>(Object[].class,form.getPageSize() ,pageindex);
		StringBuffer sql = new StringBuffer();
		if(type.equals("search")) {
			sql.append(" SELECT l.loanapply_no,l.bill_code,opr.province_name,l.operator_name,DATE_FORMAT(ts.start_date,'%Y-%m-%d') AS zxDate,b.loan_date,");
			sql.append(" TIMESTAMPDIFF(DAY,ts.start_date,b.loan_date)+2 AS useDate");
			sql.append(" FROM o2o_loanapply l,o2o_platform_regional opr,wf_type_status ts,o2o_bank_loan_data b");
			sql.append(" WHERE /**DATA_PERMISSION(l.LTD)**/ 1=1 and");
			sql.append(" opr.platform_id = l.ltd  AND ts.loanapply_no=l.loanapply_no AND ts.wf_type = 'ZX' AND l.bill_code = b.bill_code");
		} else if(type.equals("download")) {
			sql.append(" SELECT l.bill_code,opr.province_name,l.operator_name,DATE_FORMAT(ts.start_date,'%Y-%m-%d') AS zxDate,DATE_FORMAT(b.loan_date,'%Y-%m-%d') as loan_date,");
			sql.append(" TIMESTAMPDIFF(DAY,ts.start_date,b.loan_date)+2 AS useDate");
			sql.append(" FROM o2o_loanapply l,o2o_platform_regional opr,wf_type_status ts,o2o_bank_loan_data b");
			sql.append(" WHERE /**DATA_PERMISSION(l.LTD)**/ 1=1 and");
			sql.append(" opr.platform_id = l.ltd  AND ts.loanapply_no=l.loanapply_no AND ts.wf_type = 'ZX' AND l.bill_code = b.bill_code");
		}
		
		if (StringUtil.isNotBlank(form.getBillCode())) {
			sql.append(" and l.bill_code like ?");
			pageQuery.addLikeParam(form.getBillCode());
		}
		if (StringUtil.isNotBlank(form.getProvinceName())) {
			sql.append(" and opr.province_code = ?");
			pageQuery.addParam(form.getProvinceName());
		}
		if (StringUtil.isNotBlank(form.getOperatorName())) {
			sql.append(" and l.operator_name like ?");
			pageQuery.addLikeParam(form.getOperatorName());
		}
		if (form.getStartTime() != null ) {
			sql.append(" and l.create_date >= ? ");
			pageQuery.addParam(form.getStartTime());
		}
		if (form.getEndTime() != null ) {
			sql.append(" and l.create_date <= ? ");
			pageQuery.addParam(form.getEndTime());
		}
		if (form.getLoanUseStartTime() != null && form.getLoanUseEndTime() != null ) {
			sql.append(" and TIMESTAMPDIFF(DAY,ts.start_date,b.loan_date)+2 >= ? ");
			sql.append(" and TIMESTAMPDIFF(DAY,ts.start_date,b.loan_date)+2 <= ? ");
			pageQuery.addParam(form.getLoanUseStartTime());
			pageQuery.addParam(form.getLoanUseEndTime());
		}
		sql.append(" order by l.create_date	desc ");
		pageQuery.setQuerySql(sql.toString());
		return o2oJdbcSession.findDataWithPage(pageQuery);
	}
	
	
}
